package org.example.dao;

import lombok.extern.slf4j.Slf4j;
import org.example.model.entity.TableInfo;
import org.example.model.entity.ColumnInfo;
import org.example.model.entity.ColumnRelation;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 数据库信息访问类
 */
@Slf4j
public class DbInfoDao {
    private final Properties props;
    private final String sqliteDbPath;

    public DbInfoDao(Properties props, String sqliteDbPath) {
        this.props = props;
        this.sqliteDbPath = sqliteDbPath;
    }

    public Connection getSourceConnection() throws SQLException {
        return DriverManager.getConnection(
            props.getProperty("db.url"),
            props.getProperty("db.username"),
            props.getProperty("db.password"));
    }

    public Connection getSqliteConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:sqlite:" + sqliteDbPath);
    }

    public void saveTableInfo(TableInfo tableInfo) {
        String sql = "INSERT INTO table_info (table_name, row_count, column_count, table_comment, " +
                    "is_active, last_update_time) VALUES (?, ?, ?, ?, ?, ?)";
        
        try (Connection conn = getSqliteConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, tableInfo.getTableName());
            pstmt.setLong(2, tableInfo.getRowCount());
            pstmt.setInt(3, tableInfo.getColumnCount());
            pstmt.setString(4, tableInfo.getTableComment());
            pstmt.setInt(5, tableInfo.getIsActive() ? 1 : 0);
            pstmt.setTimestamp(6, Timestamp.valueOf(tableInfo.getLastUpdateTime()));
            pstmt.executeUpdate();
            
            log.info("保存表信息成功: {}", tableInfo.getTableName());
        } catch (SQLException e) {
            log.error("保存表信息失败: {}", tableInfo.getTableName(), e);
            throw new RuntimeException("保存表信息失败", e);
        }
    }

    public void saveColumnRelation(ColumnRelation relation) {
        String sql = "INSERT INTO column_relation (fk_table_name, fk_column_name, ref_table_name, " +
                    "ref_column_name, relation_name, update_rule, delete_rule, is_display, display_order) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        try (Connection conn = getSqliteConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, relation.getFkTableName());
            pstmt.setString(2, relation.getFkColumnName());
            pstmt.setString(3, relation.getRefTableName());
            pstmt.setString(4, relation.getRefColumnName());
            pstmt.setString(5, relation.getRelationName());
            pstmt.setString(6, relation.getUpdateRule());
            pstmt.setString(7, relation.getDeleteRule());
            pstmt.setInt(8, relation.getIsDisplay() ? 1 : 0);
            pstmt.setInt(9, relation.getDisplayOrder());
            pstmt.executeUpdate();
            
        } catch (SQLException e) {
            log.error("保存字段关系失败: {} -> {}", relation.getFkTableName(), relation.getRefTableName(), e);
            throw new RuntimeException("保存字段关系失败", e);
        }
    }

    public List<TableInfo> getAllTables() {
        String sql = "SELECT * FROM table_info ORDER BY table_name";
        List<TableInfo> tables = new ArrayList<>();
        
        try (Connection conn = getSqliteConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                TableInfo table = new TableInfo();
                table.setId(rs.getLong("id"));
                table.setTableName(rs.getString("table_name"));
                table.setRowCount(rs.getLong("row_count"));
                table.setColumnCount(rs.getInt("column_count"));
                table.setTableComment(rs.getString("table_comment"));
                table.setIsActive(rs.getInt("is_active") == 1);
                table.setLastUpdateTime(rs.getTimestamp("last_update_time").toLocalDateTime());
                table.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
                
                tables.add(table);
            }
        } catch (SQLException e) {
            log.error("查询表信息失败", e);
            throw new RuntimeException("查询表信息失败", e);
        }
        
        return tables;
    }


    private List<ColumnRelation> getForeignKeysByTable(Connection conn, String tableName) throws SQLException {
        String sql = "SELECT * FROM column_relation WHERE fk_table_name = ? ORDER BY display_order, fk_column_name";
        return getColumnRelations(conn, sql, tableName);
    }

    private List<ColumnRelation> getReferencedByTable(Connection conn, String tableName) throws SQLException {
        String sql = "SELECT * FROM column_relation WHERE ref_table_name = ? ORDER BY display_order, fk_table_name";
        return getColumnRelations(conn, sql, tableName);
    }

    private List<ColumnRelation> getColumnRelations(Connection conn, String sql, String tableName) throws SQLException {
        List<ColumnRelation> relations = new ArrayList<>();
        
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, tableName);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    ColumnRelation relation = new ColumnRelation();
                    relation.setId(rs.getLong("id"));
                    relation.setFkTableName(rs.getString("fk_table_name"));
                    relation.setFkColumnName(rs.getString("fk_column_name"));
                    relation.setRefTableName(rs.getString("ref_table_name"));
                    relation.setRefColumnName(rs.getString("ref_column_name"));
                    relation.setRelationName(rs.getString("relation_name"));
                    relation.setUpdateRule(rs.getString("update_rule"));
                    relation.setDeleteRule(rs.getString("delete_rule"));
                    relation.setIsDisplay(rs.getInt("is_display") == 1);
                    relation.setDisplayOrder(rs.getInt("display_order"));
                    relation.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
                    relations.add(relation);
                }
            }
        }
        return relations;
    }

    /**
     * 批量保存字段信息
     */
    public void saveColumnInfos(List<ColumnInfo> columnInfos) {

    }
} 